﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace Project_ScansiaPacific
{
    public partial class FrKV2 : DevComponents.DotNetBar.Office2007Form
    {
        private string child = " - ", MaSP1,TenSP1, MaCT1, TenCT1, SoPhieu1, SoKH1, SoXe1, LoaiGo1, KhachHang1, SLLP1, SLXP1, NgayXP1, GioXP1, NgayLP1, GioLP1, SoBB1, UserLP, UserXP, NgayInsert1;
        private double Tong1 = 0;
        private void Enable()
        {
            tab_Nhap.Visible = true;

        }
        private void Disable()
        {
            tab_Nhap.Visible = false;
        }
        private void Check_User()
        {
            Process prs=new Process();
            SqlConnection Conn = prs.Connect();
            SqlCommand cmd = new SqlCommand();
            try
            {
                Conn.Open();
                string check = @"select GroupUser from dbo.Users where UsersName='" + lb_user.Text + "'";
                cmd.Connection = Conn;
                cmd.CommandText = check;
                //MessageBox.Show(check);
                check = cmd.ExecuteScalar().ToString();
                if (check == "thongke" || check == "admin" || check == "nhaplieu")
                    Enable();
                else
                    Disable();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                Conn.Close();
                cmd.Dispose();
            }
        }
        public FrKV2()
        {
            InitializeComponent();
        }
        
        private void Hide_LP()
        {
            txt_SLLP.Enabled = false;
            txt_NgayLP.Enabled = false;
            txt_GioLP.Enabled = false;


        }
        private void UnHide_LP()
        {
            txt_SLLP.Enabled = true;
            txt_NgayLP.Enabled = true;
            txt_GioLP.Enabled = true;

        }
        private void Hide_XP()
        {
            txt_SLXP.Visible = false;
            txt_NgayXP.Visible = false;
            txt_GioXP.Visible = false;
            lb_SLXP.Visible = false;
            lb_NgayXP.Visible = false;
            lb_GioXP.Visible = false;
        }
        private void UnHide_XP()
        {
            txt_SLXP.Visible = true;
            txt_NgayXP.Visible = true;
            txt_GioXP.Visible = true;
            lb_SLXP.Visible = true;
            lb_NgayXP.Visible = true;
            lb_GioXP.Visible = true;
        }
        private DataTable dta(int n)
        {
            int j = 0;
            string colum = null;
            DataTable dt = new DataTable();
            while (j < n)
            {
                colum = j.ToString();
                dt.Columns.Add(j.ToString(), typeof(string));
                j++;
            }
            return dt;
        }
        private DataTable report()
        {
            int j = 0, k = 0, i = 0;
            string select_count = @"select count(distinct(MaCT)) from dbo.KV2_THKH", columnname, Ngay_Start, Ngay_End;
            Process prs = new Process();
            SqlConnection Conn = prs.Connect();
            DataTable dt = new DataTable();
            SqlCommand cmd = new SqlCommand();

            try
            {
                Conn.Open();
                cmd.Connection = Conn;
                cmd.CommandText = select_count;
                int row = int.Parse(cmd.ExecuteScalar().ToString());
                if (txt_datestart.Text != "" && txt_dateend.Text != "")
                {
                    Ngay_Start = txt_datestart.Text.Trim();
                    Ngay_End = txt_dateend.Text.Trim();
                    string[] temp = Ngay_Start.Split('/');
                    Ngay_Start = temp[1] + "/" + temp[0] + "/" + temp[2];
                    string[] temp2 = Ngay_End.Split('/');
                    Ngay_End = temp2[1] + "/" + temp2[0] + "/" + temp2[2];

                    //select_count = @"select MaCT,SoKH,min(NgayLP),datediff(HH,min(NgayLP),max(NgayLP)) as GioCLLP,sum(SLLP/SLChiTiet),sum(SLXP/SLChiTiet) from dbo.KV2_THKH where NgayLP>='"+Ngay_Start+" 00:00:00' and NgayLP<='"+Ngay_End+" 23:59:59' and SoKH>=83 group by MaCT,SoKH order by MaCT,SoKH";
                    if (txt_SoKH.Text != "")
                    {

                        select_count = @"select qry1.MaCT,qry1.SoKH,qry2.NgayKH_begin,qry2.GioCL,qry1.DBLP,qry1.DBXP 
                                    from (select MaCT,SoKH,sum(SLLP/SLChiTiet) as DBLP,sum(SLXP/SLChiTiet)as DBXP from dbo.KV2_THKH where SoKH>=" + txt_SoKH.Text + " and NgayLP>='" + Ngay_Start + " 00:00:00' and NgayLP<='" + Ngay_End + " 23:59:59' group by MaCT,SoKH)as qry1 ";
                        select_count += @" inner join (select SoKH,min(NgayLP)as NgayKH_begin,datediff(HH,min(NgayLP),max(NgayLP))as GioCL from dbo.KV2_THKH group by SoKH) as qry2
                                    on qry1.SoKH=qry2.SoKH
                                    order by MaCT,SoKH";
                    }
                    else
                    {
                        select_count = @"select qry1.MaCT,qry1.SoKH,qry2.NgayKH_begin,qry2.GioCL,qry1.DBLP,qry1.DBXP 
                                    from (select MaCT,SoKH,sum(SLLP/SLChiTiet) as DBLP,sum(SLXP/SLChiTiet)as DBXP from dbo.KV2_THKH where NgayLP>='" + Ngay_Start + " 00:00:00' and NgayLP<='" + Ngay_End + " 23:59:59' group by MaCT,SoKH)as qry1 ";
                        select_count += @" inner join (select SoKH,min(NgayLP)as NgayKH_begin,datediff(HH,min(NgayLP),max(NgayLP))as GioCL from dbo.KV2_THKH group by SoKH) as qry2
                                    on qry1.SoKH=qry2.SoKH
                                    order by MaCT,SoKH";
                    }
                }
                if (txt_datestart.Text == "" && txt_dateend.Text == "" && txt_SoKH.Text == "")
                {
                    select_count = @"select qry1.MaCT,qry1.SoKH,qry2.NgayKH_begin,datediff(dd,qry2.NgayKH_begin,qry1.NgayLP) as NgayLP,qry1.DBLP,qry1.DBXP,'' as CL
                                    from (select MaCT,SoKH,sum(SLLP/SLChiTiet) as DBLP,sum(SLXP/SLChiTiet)as DBXP,max(NgayLP) as NgayLP from dbo.KV2_THKH group by MaCT,SoKH)as qry1
                                    inner join (select SoKH,min(NgayLP)as NgayKH_begin from dbo.KV2_THKH group by SoKH) as qry2
                                    on qry1.SoKH=qry2.SoKH inner join (select distinct(SoKH) from 
                                    (select SoKH,'' as CT from dbo.KV2_THKH group by SoKH,MaCT Having sum(SLXP)/sum(SLLP)<0.8
                                    union all
                                    select SoKH,count(distinct(MaCT)) as CT from dbo.KV2_THKH group by SoKH having (count(distinct(MaCT))<>20 and count(distinct(MaCT))<>12 and count(distinct(MaCT))<>16 and count(distinct(MaCT))<>22 and count(distinct(MaCT))<>1)) as qry1
                                    ) as qry3 on qry1.SoKH=qry3.SoKH
                                    order by MaCT,SoKH";
                    /*select_count = @"select qry1.MaCT,qry1.SoKH,qry2.NgayKH_begin,datediff(dd,qry2.NgayKH_begin,qry1.NgayLP) as NgayLP,qry1.DBLP,qry1.DBXP,'' as CL
                                    from (select MaCT,SoKH,sum(SLLP/SLChiTiet) as DBLP,sum(SLXP/SLChiTiet)as DBXP,max(NgayLP) as NgayLP from dbo.KV2_THKH group by MaCT,SoKH)as qry1
                                    inner join (select SoKH,min(NgayLP)as NgayKH_begin from dbo.KV2_THKH group by SoKH) as qry2
                                    on qry1.SoKH=qry2.SoKH inner join (select distinct(SoKH) from dbo.KV2_THKH group by SoKH,MaCT Having sum(SLXP)/sum(SLLP)<0.8) as qry3 on qry1.SoKH=qry3.SoKH
                                    order by MaCT,SoKH";*/
                }

                DataSet ds = prs.dts(select_count);
                int dsrows = ds.Tables[0].Rows.Count;
                //MessageBox.Show(ds.Tables[0].Rows[0][0].ToString());
                select_count = @"select max(qry1.dem) from (select count(MaCT) as dem from dbo.KV2_THKH group by MaCT) as qry1";
                int rowdata = int.Parse(cmd.ExecuteScalar().ToString());
                int columndata = rowdata * 2 + 1;
                dt = dta(columndata);
                //MessageBox.Show(dt.Columns.Count.ToString());
                int count = 0;
                while (i < row)
                {
                    DataRow dtrow = dt.NewRow();
                    j = 0;
                    columnname = j.ToString();
                    dtrow[columnname] = ds.Tables[0].Rows[count][0].ToString();

                    j++;
                    for (k = count; k < dsrows; k++)
                    {
                        if (k < dsrows - 1)
                        {

                            if (ds.Tables[0].Rows[k][0].ToString() == ds.Tables[0].Rows[k + 1][0].ToString())
                            {
                                for (int m = 1; m < ds.Tables[0].Columns.Count; m++)
                                {
                                    columnname = j.ToString();
                                    dtrow[columnname] = ds.Tables[0].Rows[k][m].ToString();
                                    //MessageBox.Show(dtrow["4"].ToString());
                                    j++;
                                }
                            }
                            if (ds.Tables[0].Rows[k][0].ToString() != ds.Tables[0].Rows[k + 1][0].ToString())
                            {
                                for (int m = 1; m < ds.Tables[0].Columns.Count; m++)
                                {
                                    columnname = j.ToString();
                                    dtrow[columnname] = ds.Tables[0].Rows[k][m].ToString();
                                    //MessageBox.Show(dtrow["4"].ToString());
                                    j++;
                                }

                                //MessageBox.Show("No Khac Rui Kia");
                                while (j < columndata)
                                {
                                    //
                                    columnname = j.ToString();
                                    dtrow[columnname] = "0";
                                    j++;
                                }
                                count = k + 1;
                                dt.Rows.Add(dtrow);
                                //MessageBox.Show(dt.Rows[0][18].ToString());
                                j = 0;
                                break;

                            }
                        }
                        if (k == dsrows - 1)
                        {
                            if (ds.Tables[0].Rows[k][0].ToString() == ds.Tables[0].Rows[k - 1][0].ToString())
                            {
                                for (int m = 1; m < ds.Tables[0].Columns.Count; m++)
                                {
                                    columnname = j.ToString();
                                    dtrow[columnname] = ds.Tables[0].Rows[k][m].ToString();
                                    //MessageBox.Show(dtrow["4"].ToString());
                                    j++;
                                }
                            }
                            if (ds.Tables[0].Rows[k][0].ToString() != ds.Tables[0].Rows[k - 1][0].ToString())
                            {
                                for (int m = 1; m < ds.Tables[0].Columns.Count; m++)
                                {
                                    columnname = j.ToString();
                                    dtrow[columnname] = ds.Tables[0].Rows[k][m].ToString();
                                    //MessageBox.Show(dtrow["4"].ToString());
                                    j++;
                                }

                                //MessageBox.Show("No Khac Rui Kia");
                                while (j < columndata)
                                {
                                    //
                                    columnname = j.ToString();
                                    dtrow[columnname] = "0";
                                    j++;
                                }
                                count = k + 1;
                                dt.Rows.Add(dtrow);
                                //MessageBox.Show(dt.Rows[0][18].ToString());
                                j = 0;
                                i = row;
                                break;
                            }
                        }
                    }

                    i++;
                }
                // MessageBox.Show(dt.Rows[20][4].ToString());
                //dataGridView1.DataSource = dt;



            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            return dt;
        }
        private DataTable report_CLCTNgay()
        {
            int j = 0, k = 0, i = 0;
            string select_count = @"select count(distinct(MaCT)) from dbo.KV2_THKH", columnname, Ngay_Start, Ngay_End;
            Process prs = new Process();
            SqlConnection Conn = prs.Connect();
            DataTable dt = new DataTable();
            SqlCommand cmd = new SqlCommand();
            try
            {
                Conn.Open();
                cmd.Connection = Conn;
                cmd.CommandText = select_count;
                int row = int.Parse(cmd.ExecuteScalar().ToString());
                if (txt_datestart.Text != "" && txt_dateend.Text != "")
                {
                    Ngay_Start = txt_datestart.Text.Trim();
                    Ngay_End = txt_dateend.Text.Trim();
                    string[] temp = Ngay_Start.Split('/');
                    Ngay_Start = temp[1] + "/" + temp[0] + "/" + temp[2];
                    string[] temp2 = Ngay_End.Split('/');
                    Ngay_End = temp2[1] + "/" + temp2[0] + "/" + temp2[2];
                    select_count = @"select dbo.MaCTTest.MaCT,SLXP,Ngay from
                                    (select MaCT,sum(SLXP) as SLXP,Ngay from
                                    (select MaCT,isnull(SLXP/SLChiTiet,0) as SLXP,convert(char(10),NgayXP,101) as Ngay 
                                    from dbo.KV2_THKH where NgayXP>='" + Ngay_Start + " 00:00:00' and NgayXP<='" + Ngay_End + " 23:59:59') as qry1 group by MaCT,Ngay) as qry2 right join dbo.MaCTTest on dbo.MaCTTest.MaCT=qry2.MaCT order by dbo.MaCTTest.MaCT";
                    //MessageBox.Show(select_count);
                }
                if (txt_datestart.Text == "" || txt_dateend.Text == "")
                {
                    MessageBox.Show("Vui Lòng Nhập Ngày Cần Tìm");
                }

                DataSet ds = prs.dts(select_count);
                int dsrows = ds.Tables[0].Rows.Count;
                //MessageBox.Show(ds.Tables[0].Rows[0][0].ToString());
                select_count = @"select max(qry1.dem) from (select count(MaCT) as dem from dbo.KV2_THKH group by MaCT) as qry1";
                int rowdata = int.Parse(cmd.ExecuteScalar().ToString());
                int columndata = 3;
                dt = dta(columndata);
                //MessageBox.Show(dt.Columns.Count.ToString());
                int count = 0;
                while (i < row)
                {
                    DataRow dtrow = dt.NewRow();
                    j = 0;
                    columnname = j.ToString();
                    dtrow[columnname] = ds.Tables[0].Rows[count][0].ToString();
                    j++;
                    for (k = count; k < dsrows; k++)
                    {
                        if (k < dsrows - 1)
                        {
                            if (ds.Tables[0].Rows[k][0].ToString() == ds.Tables[0].Rows[k + 1][0].ToString())
                            {
                                for (int m = 1; m < ds.Tables[0].Columns.Count; m++)
                                {
                                    columnname = j.ToString();
                                    dtrow[columnname] = ds.Tables[0].Rows[k][m].ToString();
                                    //MessageBox.Show(dtrow["4"].ToString());
                                    j++;
                                }
                            }
                            if (ds.Tables[0].Rows[k][0].ToString() != ds.Tables[0].Rows[k + 1][0].ToString())
                            {
                                for (int m = 1; m < ds.Tables[0].Columns.Count; m++)
                                {
                                    columnname = j.ToString();
                                    dtrow[columnname] = ds.Tables[0].Rows[k][m].ToString();
                                    //MessageBox.Show(dtrow["4"].ToString());
                                    j++;
                                }

                                //MessageBox.Show("No Khac Rui Kia");
                                while (j < columndata)
                                {
                                    //
                                    columnname = j.ToString();
                                    dtrow[columnname] = "0";
                                    j++;
                                }
                                count = k + 1;
                                dt.Rows.Add(dtrow);
                                //MessageBox.Show(dt.Rows[0][18].ToString());
                                j = 0;
                                break;

                            }
                        }
                        if (k == dsrows - 1)
                        {
                            if (ds.Tables[0].Rows[k][0].ToString() == ds.Tables[0].Rows[k - 1][0].ToString())
                            {
                                for (int m = 1; m < ds.Tables[0].Columns.Count; m++)
                                {
                                    columnname = j.ToString();
                                    dtrow[columnname] = ds.Tables[0].Rows[k][m].ToString();
                                    //MessageBox.Show(dtrow["4"].ToString());
                                    j++;
                                }
                            }
                            if (ds.Tables[0].Rows[k][0].ToString() != ds.Tables[0].Rows[k - 1][0].ToString())
                            {
                                for (int m = 1; m < ds.Tables[0].Columns.Count; m++)
                                {
                                    columnname = j.ToString();
                                    dtrow[columnname] = ds.Tables[0].Rows[k][m].ToString();
                                    //MessageBox.Show(dtrow["4"].ToString());
                                    j++;
                                }

                                //MessageBox.Show("No Khac Rui Kia");
                                while (j < columndata)
                                {
                                    //
                                    columnname = j.ToString();
                                    dtrow[columnname] = "0";
                                    j++;
                                }
                                count = k+1;
                                dt.Rows.Add(dtrow);
                                //MessageBox.Show(dt.Rows[0][18].ToString());
                                j = 0;
                                i = row;
                                break;

                            }
                        }
                        i++;
                    }
                }
                // MessageBox.Show(dt.Rows[20][4].ToString());
                //dataGridView1.DataSource = dt;



            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            return dt;
        }
        private void bt_search_Click(object sender, EventArgs e)
        {
            DataTable dt = new DataTable();
            dt = report();
            dataGridView1.DataSource = dt;
        }
        private void textBox3_TextChanged(object sender, EventArgs e)
        {

        }
        private void GroupTK()
        {
            rd_LP.Visible = true;
            rd_XP.Visible = true;
            bt_cancel.Visible = true;
            bt_edit.Visible = true;
            bt_insert.Visible = true;
            bt_save.Visible = true;
        }

        private void FrKV2_Load(object sender, EventArgs e)
        {
            combo_load();
            dataGridView2.ColumnHeadersDefaultCellStyle.BackColor = Color.AliceBlue;
            dataGridView2.EnableHeadersVisualStyles = false;
            //string Users = @"select GroupUser from dbo.Users where UsersName='" + lb_user.Text + "'";
           
            try
            {
                /*Data dta = new Data();
                SqlConnection Conn = dta.Connect();
                Conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = Conn;
                cmd.CommandText = Users;
                MessageBox.Show(Users);
                Users = cmd.ExecuteScalar().ToString();
                MessageBox.Show(Users);
                if (Users == "thongke" || Users == "admin" || Users == "nhaplieu")
                {
                    MessageBox.Show("aaaaa");
                    Enable();
                }
                else
                    Disable();*/

                //Hide_LP();
                Hide_XP();
                dataGridView2.Columns["SLLP"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                dataGridView2.Columns["SLXP"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                //Check_User();
            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        private void combo_load()
        {
            Data dta = new Data();
            FormProcess Frp = new FormProcess();
            string sql_load = @"select * from dbo.Load_cbSP";
            Frp.cb1(cb_MaSP, sql_load, 1, 0);
            Frp.cb1(cb_MaSPreportTH, sql_load, 1, 0);
            //Frp.cb1(cb_MaSPreport, sql_load, 1, 0);

        }

        private void cb_MaSP_SelectedIndexChanged(object sender, EventArgs e)
        {
            Process prs = new Process();
            FormProcess Frp = new FormProcess();
            MaSP1 = prs.substring_right(cb_MaSP.Text, child);
            cb_MaCT.Items.Clear();
            cb_MaCT.Text = "";
            string sql_load = @"select MaChiTiet,TenChiTiet from dbo.ChiTietSP where MaSP='" + MaSP1 + "'";
            Frp.cb1(cb_MaCT, sql_load, 1, 0);
        }

        private void txt_SLLP_TextChanged(object sender, EventArgs e)
        {
            FormProcess Frp = new FormProcess();
            Frp.checktext_number(txt_SLLP);
        }

        private void txt_SLXP_TextChanged(object sender, EventArgs e)
        {
            FormProcess Frp = new FormProcess();
            Frp.checktext_number(txt_SLXP);
        }
        private void add_grid()
        {
            try
            {
                if (txt_GioLP.TextLength != 4)
                    MessageBox.Show("Nhập thời gian chưa đúng: hhmm");
                else
                {
                    if (cb_MaCT.Text == "" || cb_MaSP.Text == "" || txt_SLLP.Text == "" || txt_SoKeHoach.Text == "" || txt_SoPhieu.Text == "" || txt_NgayLP.Text == "" || txt_GioLP.Text == "")
                        MessageBox.Show("Nhập Thiếu Thông Tin");
                    else
                    {
                        Process prs = new Process();
                        MaSP1 = prs.substring_left(cb_MaSP.Text, child);
                        TenCT1 = prs.substring_left(cb_MaCT.Text, child);
                        MaCT1 = prs.substring_right(cb_MaCT.Text, child);
                        SoKH1 = txt_SoKeHoach.Text.Trim();
                        SoPhieu1 = txt_SoPhieu.Text.Trim();
                        SoXe1 = txt_SoXe.Text.Trim();
                        SLLP1 = txt_SLLP.Text;
                        GioLP1 = txt_GioLP.Text.Substring(0, 2) + ":" + txt_GioLP.Text.Substring(2, 2) + ":00";
                        NgayLP1 = txt_NgayLP.Text.Trim();
                        NgayInsert1 = txt_NgayLP.Text.Substring(3, 2) + "/" + txt_NgayLP.Text.Substring(0, 2) + "/" + txt_NgayLP.Text.Substring(6, 2);
                        KhachHang1 = txt_KH.Text;
                        LoaiGo1 = txt_LoaiGo.Text;
                        SoBB1 = txt_SoBB.Text;
                        NgayLP1 = NgayLP1 + " " + GioLP1;
                        string[] temp = txt_NgayLP.Text.Split('/');
                        NgayInsert1 = temp[1] + "/" + temp[0] + "/" + temp[2];
                        NgayInsert1 = NgayInsert1 + " " + GioLP1;
                        //MessageBox.Show(GioLP1 + " " + NgayLP1);
                        if (MaSP1 == "" || MaCT1 == "")
                        {
                            MessageBox.Show("Thông Tin Nhập Không Chính Xác");

                        }
                        else
                        {
                            int i = 0;
                            if (dataGridView2.Rows.Count > 1)
                            {
                                while (i < dataGridView2.Rows.Count - 1)
                                {
                                    if (SoPhieu1 != dataGridView2.Rows[i].Cells["SoPhieu"].Value.ToString())
                                        i++;
                                    else
                                        break;
                                }
                            }
                            if (i == dataGridView2.Rows.Count - 1 || dataGridView2.Rows.Count == 1)
                            //dataGridView1.Rows.Add(MaSP1, MaCT1, TenCT1, SoKH1, SoPhieu1, SoXe1, SLLP1, NgayLP1, GioLP1, KhachHang1, LoaiGo1);
                            {
                                dataGridView2.Rows.Add(MaSP1, MaCT1, TenCT1, SoKH1, SoPhieu1, SoXe1, SLLP1, NgayLP1, KhachHang1, LoaiGo1, SoBB1, NgayInsert1, "", "");
                                Tong1 = double.Parse(lb_Tong.Text);
                                Tong1 += double.Parse(SLLP1);
                                lb_Tong.Text = Tong1.ToString();
                                lb_Tong.Text = string.Format("{0:###,###}", Tong1);
                                cb_MaCT.Text = "";
                                cb_MaCT.Focus();
                                //txt_SoBB.Text == "";
                                txt_SoPhieu.Text = "";
                                txt_SoXe.Text = "";
                                txt_SLLP.Text = "";
                                txt_GioLP.Text = "";
                                txt_SoPhieu.Text = (double.Parse(SoPhieu1) + 1).ToString();
                            }
                            else
                                MessageBox.Show("Số phiếu này đã nhập rồi");

                        }
                    }

                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        private void resetPanel()
        {
            cb_MaSP.Focus();
            cb_MaSP.Text = "";
            cb_MaCT.Items.Clear();
            cb_MaCT.Text = "";
            //txt_SoBB.Text == "";
            txt_SoPhieu.Text = "";
            txt_SoXe.Text = "";
            txt_SLLP.Text = "";
            txt_GioLP.Text = "";
        }
        private void bt_insert_Click(object sender, EventArgs e)
        {
            add_grid();
        }

        
        private void save()
        {
            int i;
            Data dta = new Data();
            SqlConnection Conn = dta.Connect();
            SqlCommand cmd = new SqlCommand();
            string insert_LXP = @"insert into dbo.KV2_LXP(MaChiTiet,SoKH,SoBB,SoPhieu,SoXe,KhachHang,LoaiGo,SLLP,NgayLP,UserLP)values(N'";
            if (dataGridView2.Rows.Count > 1)
            {
                try
                {
                    Conn.Open();
                    cmd.Connection = Conn;
                    for (i = dataGridView2.Rows.Count - 2; i >= 0; i--)
                    {
                        MaCT1 = dataGridView2.Rows[i].Cells["MaCT"].Value.ToString();
                        SoKH1 = dataGridView2.Rows[i].Cells["SoKH"].Value.ToString();
                        SoBB1 = dataGridView2.Rows[i].Cells["SoBB"].Value.ToString();
                        SoPhieu1 = dataGridView2.Rows[i].Cells["SoPhieu"].Value.ToString();
                        SoXe1 = dataGridView2.Rows[i].Cells["SoXe"].Value.ToString();
                        KhachHang1 = dataGridView2.Rows[i].Cells["KhachHang"].Value.ToString();
                        LoaiGo1 = dataGridView2.Rows[i].Cells["LoaiGo"].Value.ToString();
                        SLLP1 = double.Parse(dataGridView2.Rows[i].Cells["SLLP"].Value.ToString()).ToString();
                        NgayLP1 = dataGridView2.Rows[i].Cells["NgayInsert"].Value.ToString();
                        //GioLP1 = dataGridView2.Rows[i].Cells["GioLP"].Value.ToString();
                        UserLP = lb_user.Text;
                        insert_LXP = @"insert into dbo.KV2_LXP(MaChiTiet,SoKH,SoBB,SoPhieu,SoXe,KhachHang,LoaiGo,SLLP,NgayLP,UserLP)values(N'" + MaCT1 + "',N'" + SoKH1 + "',N'" + SoBB1 + "',N'" + SoPhieu1 + "',N'" + SoXe1 + "',N'" + KhachHang1 + "','" + LoaiGo1 + "'," + SLLP1 + ",'" + NgayInsert1 + "',N'" + UserLP + "')";
                        cmd.CommandText = insert_LXP;
                        cmd.ExecuteNonQuery();
                        dataGridView2.Rows.RemoveAt(i);
                        
                    }
                    MessageBox.Show("Dữ Liệu được lưu thành công");
                    lb_Tong.Text = "0";


                }
                catch (Exception ex)
                {
                    string s = "insert duplicate key";
                    string FK = "FOREIGN KEY constraint";
                    string s1 = ex.Message.ToString();
                    if (s1.Contains(s))
                    { MessageBox.Show("Insert không thành công \n" + "Mã đã tồn tại "); }
                    else if (s1.Contains(FK))
                    { MessageBox.Show("Nhập sai thông tin trong Combobox"); }
                    else
                        MessageBox.Show(ex.Message);
                    MessageBox.Show("Đã xảy ra lỗi khi thêm dữ liệu, vui lòng kiểm tra lại");
                }
                finally
                {
                    Conn.Close();
                    cmd.Dispose();
                }
            }
            else
                MessageBox.Show("Không có dữ liệu, vui lòng nhập trước khi lưu");
        }
        private void updateXP()
        {
            Data dta = new Data();
            SqlConnection Conn = dta.Connect();
            SqlCommand cmd = new SqlCommand();
            try
            {
                GioXP1 = txt_GioXP.Text.Substring(0, 2) + ":" + txt_GioXP.Text.Substring(2, 2) + ":00";
                SoPhieu1 = txt_SoPhieu.Text.Trim();
                SLXP1 = double.Parse(txt_SLXP.Text).ToString();
                NgayXP1 = txt_NgayXP.Text.Trim();
                string[] tempXP = NgayXP1.Split('/');
                NgayXP1 = tempXP[1] + "/" + tempXP[0] + "/" + tempXP[2];
                NgayXP1 = NgayXP1 + " " + GioXP1;
                UserXP = lb_user.Text;

                Conn.Open();
                string update_XP = @"update dbo.KV2_LXP set SLXP=" + SLXP1 + ",NgayXP='" + NgayXP1 + "',UserXP='" + UserXP + "' where SoPhieu='" + SoPhieu1 + "'";
                //MessageBox.Show(update_XP);
                cmd.CommandText = update_XP;
                cmd.Connection = Conn;
                cmd.ExecuteNonQuery();
                resetPanel();
                txt_SLXP.Text = "";
                txt_GioXP.Text = "";
                txt_SoPhieu.Focus();
                if (dataGridView2.Rows.Count > 1)
                    dataGridView2.Rows.RemoveAt(0);
            }
            catch (Exception ex)
            {
                //MessageBox.Show("Đã xảy ra lỗi khi thêm dữ liệu, Vui lòng kiểm tra lại");
                MessageBox.Show(ex.Message);
            }
        }

        private void bt_save_Click(object sender, EventArgs e)
        {
            if (rd_LP.Checked == true)
                save();
            if (rd_XP.Checked == true)
                updateXP();
            bt_edit.Enabled = false;
        }

        private void rd_LP_CheckedChanged(object sender, EventArgs e)
        {
            UnHide_LP();
            Hide_XP();
        }

        private void rd_XP_CheckedChanged(object sender, EventArgs e)
        {
            UnHide_XP();
            Hide_LP();
        }
        private void edit()
        {
            try
            {
                string datecheck;
                int date_count;
                Process prs = new Process();
                SqlConnection Conn = prs.Connect();
                SqlCommand cmd = new SqlCommand();
                DialogResult d = MessageBox.Show("Bạn có chắc muốn sửa Thông Tin có Số Phiếu " + txt_SoPhieu.Text, "Thông Báo", MessageBoxButtons.OKCancel);
                MaCT1 = prs.substring_right(cb_MaCT.Text, child);
                if (MaCT1 == "")
                    MaCT1 = cb_MaCT.Text;
                SoKH1 = txt_SoKeHoach.Text.Trim();
                SoBB1 = txt_SoBB.Text;
                SoPhieu1 = txt_SoPhieu.Text.Trim();
                SoXe1 = txt_SoXe.Text.Trim();
                KhachHang1 = txt_KH.Text;
                LoaiGo1 = txt_LoaiGo.Text;
                SLLP1 = double.Parse(txt_SLLP.Text).ToString();
                GioLP1 = txt_GioLP.Text.Substring(0, 2) + ":" + txt_GioLP.Text.Substring(2, 2) + ":00";
                NgayLP1 = txt_NgayLP.Text;
                string[] temp = NgayLP1.Split('/');
                NgayLP1 = temp[1] + "/" + temp[0] + "/" + temp[2];
                //NgayLP1 = txt_NgayLP.Text.Substring(2, 2) + "/" + txt_NgayLP.Text.Substring(0, 2) + "/" + txt_NgayLP.Text.Substring(4, 2);
                NgayLP1 = NgayLP1 + " " + GioLP1;
                Conn.Open();
                cmd.Connection = Conn;
                datecheck = @"select datediff(dd,Ngay,getdate()) from dbo.KV2_LXP where SoPhieu='"+SoPhieu1+"'";
                if (d == DialogResult.OK)
                {
                    cmd.CommandText = datecheck;
                    date_count = int.Parse(cmd.ExecuteScalar().ToString());
                    if (date_count <= 1)
                    {
                        if (rd_LP.Checked == true)
                        {
                            string update_XP = @"update dbo.KV2_LXP set MaChiTiet='" + MaCT1 + "',SoKH='" + SoKH1 + "',SoBB='" + SoBB1 + "',SoXe='" + SoXe1 + "',KhachHang='" + KhachHang1 + "',LoaiGo='" + LoaiGo1 + "',SLLP=";
                            update_XP += SLLP1 + ",NgayLP='" + NgayLP1 + "',Useredit='" + UserXP + "' where SoPhieu='" + SoPhieu1 + "'";
                           // MessageBox.Show(update_XP);
                            cmd.CommandText = update_XP;
                            cmd.ExecuteNonQuery();
                            MessageBox.Show("Chỉnh Sửa Thành Công");
                        }
                        if (rd_XP.Checked == true)
                        {
                            if (txt_SLXP.Text != "" && txt_NgayXP.Text != "" && txt_NgayLP.Text != "")
                            {
                                GioXP1 = txt_GioXP.Text.Substring(0, 2) + ":" + txt_GioXP.Text.Substring(2, 2) + ":00";
                                SLXP1 = double.Parse(txt_SLXP.Text).ToString();
                                NgayXP1 = txt_NgayXP.Text.Trim();
                                string[] tempXP = NgayXP1.Split('/');
                                NgayXP1 = tempXP[1] + "/" + tempXP[0] + "/" + tempXP[2];
                                NgayXP1 = NgayXP1 + " " + GioXP1;
                                UserXP = lb_user.Text;
                                string update_XP = @"update dbo.KV2_LXP set MaChiTiet='" + MaCT1 + "',SoKH='" + SoKH1 + "',SoBB='" + SoBB1 + "',SoXe='" + SoXe1 + "',KhachHang='" + KhachHang1 + "',LoaiGo='" + LoaiGo1 + "',SLXP=";
                                update_XP += SLXP1 + ",NgayXP='" + NgayXP1 + "',Useredit='" + UserXP + "' where SoPhieu='" + SoPhieu1 + "'";
                                //MessageBox.Show(update_XP);
                                cmd.CommandText = update_XP;
                                cmd.ExecuteNonQuery();
                                MessageBox.Show("Chỉnh Sửa Thành Công");
                            }
                            else
                                MessageBox.Show("Nhập thiếu thông tin");
                        }
                    }
                    else
                        MessageBox.Show("Quá thời gian để chỉnh sửa");
                    Conn.Close();
                    cmd.Dispose();
                    dataGridView2.Rows.RemoveAt(0);
                    resetPanel();
                    bt_edit.Enabled = false;
                }
            }
            catch (Exception ex)
            {
                string s = "The conversion of a char data type to a datetime";
                string s1 = ex.Message.ToString();
                if (s1.Contains(s))
                { MessageBox.Show("Ngày Tháng Nhập chưa đúng"); }
                MessageBox.Show(ex.Message);
            }

        }

        private void bt_edit_Click(object sender, EventArgs e)
        {
            if (rd_LP.Checked == false && rd_XP.Checked == false)
                MessageBox.Show("Chỉnh sửa cho LP hay XP");
            else
                edit();
        }
        private void search()
        {
            if (txt_SoPhieu.Text != "")
            {
                Process prs = new Process();
                SqlConnection Conn = prs.Connect();
                SqlCommand cmd = new SqlCommand();
                string select = null;
                try
                {
                    if (dataGridView2.Rows.Count > 1)
                        dataGridView2.Rows.RemoveAt(0);
                    if (txt_SoPhieu.Text != "")
                    {
                        select = @"select KV2_LXP.MaChiTiet,ChiTietSP.TenChiTiet,ChiTietSP.MaSP,ChiTietSP.TenSP,KV2_LXP.SoKH,KV2_LXP.SoBB,KV2_LXP.SoPhieu,KV2_LXP.SoXe,KV2_LXP.KhachHang,KV2_LXP.LoaiGo,KV2_LXP.SLLP,convert(char(10),KV2_LXP.NgayLP,103)+' '+convert(char(8),KV2_LXP.NgayLP,108) as NgayLP,KV2_LXP.SLXP,convert(char(10),KV2_LXP.NgayXP,103)+' '+convert(char(8),KV2_LXP.NgayXP,108) as NgayXP from dbo.KV2_LXP inner join dbo.ChiTietSP on dbo.KV2_LXP.MaChiTiet=dbo.ChiTietSP.MaChiTiet where SoPhieu='" + txt_SoPhieu.Text + "'";
                    }

                    DataSet ds = new DataSet();
                    ds = prs.dts(select);
                    //dataGridView2.DataSource = ds.Tables[0];
                    MaSP1 = ds.Tables[0].Rows[0]["MaSP"].ToString();
                    TenSP1 = ds.Tables[0].Rows[0]["TenSP"].ToString();
                    MaCT1 = ds.Tables[0].Rows[0]["MaChiTiet"].ToString();
                    TenCT1 = ds.Tables[0].Rows[0]["TenChiTiet"].ToString();
                    SoKH1 = ds.Tables[0].Rows[0]["SoKH"].ToString();
                    SoPhieu1 = ds.Tables[0].Rows[0]["SoPhieu"].ToString();
                    SoXe1 = ds.Tables[0].Rows[0]["SoXe"].ToString();
                    SLLP1 = ds.Tables[0].Rows[0]["SLLP"].ToString();
                    NgayLP1 = ds.Tables[0].Rows[0]["NgayLP"].ToString();
                    KhachHang1 = ds.Tables[0].Rows[0]["KhachHang"].ToString();
                    LoaiGo1 = ds.Tables[0].Rows[0]["LoaiGo"].ToString();
                    SLXP1 = ds.Tables[0].Rows[0]["SLXP"].ToString();
                    NgayXP1 = ds.Tables[0].Rows[0]["NgayXP"].ToString();
                    SoBB1 = ds.Tables[0].Rows[0]["SoBB"].ToString();
                    dataGridView2.Rows.Add(TenSP1, MaCT1, TenCT1, SoKH1, SoPhieu1, SoXe1, SLLP1, NgayLP1, KhachHang1, LoaiGo1, SoBB1, "", SLXP1, NgayXP1);
                    if (dataGridView2.Rows.Count > 1)
                    {
                        Display(0);
                        bt_edit.Enabled = true;
                    }
                    else
                        MessageBox.Show("Không có dữ liệu");

                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
            else
                MessageBox.Show("Nhập Số Phiếu để tìm kiếm");
        }
        private void delete()
        {
            DialogResult d = MessageBox.Show("Bạn có chắc muốn xóa Thông Tin có Số Phiếu " + txt_SoPhieu.Text, "Thông Báo", MessageBoxButtons.OKCancel);
            if (d == DialogResult.OK)
            {
                try
                {
                    string delete = @"delete from dbo.KV2_LXP where SoPhieu='" + txt_SoPhieu.Text + "'";
                    Data dta = new Data();
                    SqlConnection Conn = dta.Connect();
                    Conn.Open();
                    SqlCommand cmd = new SqlCommand(delete, Conn);
                    cmd.ExecuteNonQuery();
                    MessageBox.Show("Dữ liệu được xóa thành công");
                    resetPanel();
                    Conn.Close();
                    cmd.Dispose();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }

        }

        private void buttonX1_Click(object sender, EventArgs e)
        {
            search();
            txt_SoPhieu.Focus();
        }
        private void Display(int i)
        {
            cb_MaSP.Text = dataGridView2.Rows[i].Cells["TenSP"].Value.ToString();
            cb_MaCT.Text = dataGridView2.Rows[i].Cells["TenCT"].Value.ToString() + " - " + dataGridView2.Rows[i].Cells["MaCT"].Value.ToString();
            txt_SoKH.Text = dataGridView2.Rows[i].Cells["SoKH"].Value.ToString();
            txt_SoBB.Text = dataGridView2.Rows[i].Cells["SoBB"].Value.ToString();
            txt_SoPhieu.Text = dataGridView2.Rows[i].Cells["SoPhieu"].Value.ToString();
            txt_SoXe.Text = dataGridView2.Rows[i].Cells["SoXe"].Value.ToString();
            txt_KH.Text = dataGridView2.Rows[i].Cells["KhachHang"].Value.ToString();
            txt_SoKeHoach.Text = dataGridView2.Rows[i].Cells["SoKH"].Value.ToString();
            txt_LoaiGo.Text = dataGridView2.Rows[i].Cells["LoaiGo"].Value.ToString();
            txt_SLLP.Text = dataGridView2.Rows[i].Cells["SLLP"].Value.ToString();
            NgayLP1 = dataGridView2.Rows[i].Cells["NgayLP"].Value.ToString();
            NgayLP1 = NgayLP1.Substring(0, 10);
            //NgayLP1 = NgayLP1.Substring(3, 2) + "/" + NgayLP1.Substring(0, 2) + "/" + NgayLP1.Substring(6, 2);
            txt_NgayLP.Text = NgayLP1;
            GioLP1 = dataGridView2.Rows[i].Cells["NgayLP"].Value.ToString();
            //MessageBox.Show(GioLP1);
            GioLP1 = GioLP1.Substring(11, 2).ToString() + GioLP1.Substring(14, 2);
            //MessageBox.Show(GioLP1);
            txt_GioLP.Text = GioLP1;
            if (dataGridView2.Rows[i].Cells["SLXP"].Value.ToString() != "")
            {
                txt_SLXP.Text = dataGridView2.Rows[i].Cells["SLXP"].Value.ToString();
                NgayXP1 = dataGridView2.Rows[i].Cells["NgayXP"].Value.ToString();
                NgayXP1 = NgayXP1.Substring(0, 10);
                //NgayXP1 = NgayXP1.Substring(3, 2) + "/" + NgayXP1.Substring(0, 2) + "/" + NgayXP1.Substring(6, 2);
                txt_NgayXP.Text = NgayXP1;
                GioXP1 = dataGridView2.Rows[i].Cells["NgayXP"].Value.ToString();
                GioXP1 = GioXP1.Substring(11, 2).ToString() + GioXP1.Substring(14, 2);
                txt_GioXP.Text = GioXP1;
            }
            else
            {
                txt_SLXP.Text = "";
                txt_NgayXP.Text = "";
                txt_GioXP.Text = "";
            }
        }

        private void FrKV2_KeyDown(object sender, KeyEventArgs ex)
        {
            if (ex.KeyData == Keys.Enter)
            {
                SendKeys.Send("{TAB}");
            }
            if (ex.KeyCode == Keys.F11)
            {
                bt_save_Click(null, null);
            }
            if (ex.KeyCode == Keys.F5)
            {
                report_CTChuaXP();
            }
        }

        private void FrKV2_KeyUp(object sender, KeyEventArgs e)
        {
            if (e.Control)
            {
                if (e.KeyCode.Equals(Keys.F))
                    buttonX1_Click(null, null);
            }
        }

        private void bt_cancel_Click(object sender, EventArgs e)
        {
            delete();
        }

      
        private void Data_report()
        {
            /*if (txt_dateendRp.Text != "" || txt_datestartRp.Text != "" || txt_MaSPreport.Text != "" || txt_SoKHreport.Text != "" || txt_SoPhieuReport.Text != "")
            {
                try
                {
                    string select = @"select * from dbo.KV2_NhapLPXP ";
                    if (txt_SoPhieuReport.Text != "")
                    {
                        select += " where SoPhieu='" + txt_SoPhieuReport.Text + "'";
                    }
                    else
                    {
                        if (txt_SoKHreport.Text != "")
                        {
                            select += " where MaKeHoach='" + txt_SoKHreport.Text + "'";
                        }
                        else
                        {
                            if (txt_datestartRp.Text != "" && txt_dateendRp.Text != "")
                            {
                                string Ngay_Start = txt_datestartRp.Text;
                                string Ngay_End = txt_dateendRp.Text;
                                string[] temp = Ngay_Start.Split('/');
                                Ngay_Start = temp[1] + "/" + temp[0] + "/" + temp[2];
                                string[] temp2 = Ngay_End.Split('/');
                                Ngay_End = temp2[1] + "/" + temp2[0] + "/" + temp2[2];
                                
                                if (txt_MaSPreport.Text == "")
                                {
                                    if (rd_LenPhieu.Checked == true)
                                        select += " where NgayLP>='" + Ngay_Start + " 00:00:00' and NgayLP<='" + Ngay_End + " 23:59:59'";
                                    if (rd_XuongPhieu.Checked == true)
                                        select += " where NgayXP>='" + Ngay_Start + " 00:00:00' and NgayXP<='" + Ngay_End + " 23:59:59'";
                                }
                                if (txt_MaSPreport.Text != "")
                                {
                                    if (rd_LenPhieu.Checked == true)
                                        select += " where NgayLP>='" + Ngay_Start + " 00:00:00' and NgayLP<='" + Ngay_End + " 23:59:59' and MaSP='" + txt_MaSPreport.Text + "'";
                                    if (rd_XuongPhieu.Checked == true)
                                        select += " where NgayXP>='" + Ngay_Start + " 00:00:00' and NgayXP<='" + Ngay_End + " 23:59:59' and MaSP='" + txt_MaSPreport.Text + "'";
                                }
                            }
                        }
                    }

                    Process prs = new Process();
                    DataSet ds = new DataSet();
                    ds = prs.dts(select);
                    //report_LXP(ds);
                    ds.Clear();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
            else
                MessageBox.Show("Vui lòng nhập thông tin cần tìm");*/
        }
        private void bt_printer_Click(object sender, EventArgs e)
        {
            Data_report();
        }
        private void cb_MaSPreportTH_SelectedIndexChanged(object sender, EventArgs e)
        {
            Process prs = new Process();
            FormProcess Frp = new FormProcess();
            string MaSPreport = prs.substring_right(cb_MaSPreportTH.Text, child);
            cb_MaCTReportTH.Items.Clear();
            cb_MaCTReportTH.Text = "";
            string sql_load = @"select MaChiTiet,TenChiTiet from dbo.ChiTietSP where MaSP='" + MaSPreport + "'";
            Frp.cb1(cb_MaCTReportTH, sql_load, 1, 0);
        }
        private void reportTH()
        {
            string Ngay_Start = txt_datestartRpTH.Text;
            string Ngay_End = txt_dateendRpTH.Text;
            try
            {
                string MaSPreport, MaCTreport;
                Process prs = new Process();
                FormProcess Frp = new FormProcess();
                string select = null;
                if (cb_MaCTReportTH.Text == "" && cb_MaSPreportTH.Text == "" && txt_MaKHReportTH.Text == "" && txt_dateendRpTH.Text == "" && txt_datestartRpTH.Text == "")
                    MessageBox.Show("Vui Lòng nhập thông tin cần tìm");
                else
                {
                    if (txt_MaKHReportTH.Text != "")
                    {
                        select = @"select qry1.MaKeHoach,qry1.MaChiTiet,qry2.TenSP,qry2.TenChiTiet,qry1.SLLP,qry1.NgayLP,qry1.SLXP,qry1.NgayXP,datediff(dd,qry1.NgayLP,qry1.NgayXP) as NgayCL,(qry1.SLLP*qry2.ChieuDaySC*qry2.ChieuRongSC*qry2.ChieuDaiSC*qry2.SoLuongCT/1000000000) as SoKhoi from
                                (select MaKeHoach,MaChiTiet,sum(SLLP/SoLuongCT) as SLLP,max(NgayLP) as NgayLP,sum(isnull(SLXP,0)/SoLuongCT) as SLXP,max(NgayXP) as NgayXP from dbo.KV2_NhapLPXP group by MaKeHoach,MaChiTiet) as qry1 inner join (select MaSP,TenSP,MaChiTiet,TenChiTiet,SoLuongCT,ChieuDaySC,ChieuRongSC,ChieuDaiSC from dbo.ChiTietSP) as qry2 on qry1.MaChiTiet=qry2.MaChiTiet";
                        select += " where MaKeHoach='" + txt_MaKHReportTH.Text + "'";
                    }
                    else
                    {
                        if (txt_datestartRpTH.Text != "" && txt_dateendRpTH.Text != "")
                        {


                            string[] temp = Ngay_Start.Split('/');
                            Ngay_Start = temp[1] + "/" + temp[0] + "/" + temp[2];
                            string[] temp2 = Ngay_End.Split('/');
                            Ngay_End = temp2[1] + "/" + temp2[0] + "/" + temp2[2];
                            if (cb_MaSPreportTH.Text == "")
                            {
                                if (rd_XuongPhieuRpTH.Checked == true)
                                {
                                    select = @" SELECT     qry1.MaKeHoach, qry1.MaChiTiet, qry2.TenSP, qry2.TenChiTiet, qry1.SLLP,qry1.NgayLP, qry1.SLXP,qry1.NgayXP, 
                                            qry1.SLLP * qry2.ChieuDaySC * qry2.ChieuRongSC * qry2.ChieuDaiSC * qry2.SoLuongCT / 1000000000 AS SoKhoi
                                             FROM         (SELECT     MaKeHoach, MaChiTiet, SUM(SLLP / SoLuongCT) AS SLLP,max(NgayLP) as NgayLP, SUM(ISNULL(SLXP, 0) / SoLuongCT) AS SLXP,max(NgayXP) as NgayXP
                                             FROM          dbo.KV2_NhapLPXP
                                             WHERE      (NgayXP >= '" + Ngay_Start + " 00:00:00') AND (NgayXP <= '" + Ngay_End + " 23:59:59')";
                                    select += @" GROUP BY MaKeHoach, MaChiTiet) AS qry1 inner JOIN
                                            (SELECT     MaSP, TenSP, MaChiTiet, TenChiTiet, SoLuongCT, ChieuDaySC, ChieuRongSC, ChieuDaiSC
                                            FROM          dbo.ChiTietSP) AS qry2 ON qry1.MaChiTiet = qry2.MaChiTiet";
                                }
                                if (rd_LenPhieuRpTH.Checked == true)
                                {
                                    select = @" SELECT     qry1.MaKeHoach, qry1.MaChiTiet, qry2.TenSP, qry2.TenChiTiet, qry1.SLLP,qry1.NgayLP, qry1.SLXP,qry1.NgayXP, 
                                            qry1.SLLP * qry2.ChieuDaySC * qry2.ChieuRongSC * qry2.ChieuDaiSC * qry2.SoLuongCT / 1000000000 AS SoKhoi
                                             FROM         (SELECT     MaKeHoach, MaChiTiet, SUM(SLLP / SoLuongCT) AS SLLP,max(NgayLP) as NgayLP, SUM(ISNULL(SLXP, 0) / SoLuongCT) AS SLXP,max(NgayXP) as NgayXP
                                             FROM          dbo.KV2_NhapLPXP
                                             WHERE      (NgayLP >= '" + Ngay_Start + " 00:00:00') AND (NgayLP <= '" + Ngay_End + " 23:59:59')";
                                    select += @" GROUP BY MaKeHoach, MaChiTiet) AS qry1 inner JOIN
                                            (SELECT     MaSP, TenSP, MaChiTiet, TenChiTiet, SoLuongCT, ChieuDaySC, ChieuRongSC, ChieuDaiSC
                                            FROM          dbo.ChiTietSP) AS qry2 ON qry1.MaChiTiet = qry2.MaChiTiet";
                                }
                            }
                            if (cb_MaSPreportTH.Text != "")
                            {
                                MaSPreport = prs.substring_right(cb_MaSPreportTH.Text, child);
                                if (MaSPreport == "")
                                    MessageBox.Show("Nhập sai thông tin");
                                else
                                {
                                    if (cb_MaCTReportTH.Text == "")
                                    {
                                        if (rd_XuongPhieuRpTH.Checked == true)
                                        {
                                            select = @" SELECT     qry1.MaKeHoach, qry1.MaChiTiet, qry2.TenSP, qry2.TenChiTiet, qry1.SLLP,qry1.NgayLP, qry1.SLXP,qry1.NgayXP, 
                                            qry1.SLLP * qry2.ChieuDaySC * qry2.ChieuRongSC * qry2.ChieuDaiSC * qry2.SoLuongCT / 1000000000 AS SoKhoi
                                             FROM         (SELECT     MaKeHoach, MaChiTiet, SUM(SLLP / SoLuongCT) AS SLLP,max(NgayLP) as NgayLP, SUM(ISNULL(SLXP, 0) / SoLuongCT) AS SLXP,max(NgayXP) as NgayXP
                                             FROM          dbo.KV2_NhapLPXP
                                             WHERE      (NgayXP >= '" + Ngay_Start + " 00:00:00') AND (NgayXP <= '" + Ngay_End + " 23:59:59') and MaSP='" + MaSPreport + "'";
                                            select += @" GROUP BY MaKeHoach, MaChiTiet) AS qry1 inner JOIN
                                            (SELECT     MaSP, TenSP, MaChiTiet, TenChiTiet, SoLuongCT, ChieuDaySC, ChieuRongSC, ChieuDaiSC
                                            FROM          dbo.ChiTietSP) AS qry2 ON qry1.MaChiTiet = qry2.MaChiTiet";
                                        }
                                        if (rd_LenPhieuRpTH.Checked == true)
                                        {
                                            select = @" SELECT     qry1.MaKeHoach, qry1.MaChiTiet, qry2.TenSP, qry2.TenChiTiet, qry1.SLLP,qry1.NgayLP, qry1.SLXP,qry1.NgayXP, 
                                            qry1.SLLP * qry2.ChieuDaySC * qry2.ChieuRongSC * qry2.ChieuDaiSC * qry2.SoLuongCT / 1000000000 AS SoKhoi
                                             FROM         (SELECT     MaKeHoach, MaChiTiet, SUM(SLLP / SoLuongCT) AS SLLP,max(NgayLP) as NgayLP, SUM(ISNULL(SLXP, 0) / SoLuongCT) AS SLXP,max(NgayXP) as NgayXP
                                             FROM          dbo.KV2_NhapLPXP
                                             WHERE      (NgayLP >= '" + Ngay_Start + " 00:00:00') AND (NgayLP <= '" + Ngay_End + " 23:59:59') and MaSP='" + MaSPreport + "'";
                                            select += @" GROUP BY MaKeHoach, MaChiTiet) AS qry1 inner JOIN
                                            (SELECT     MaSP, TenSP, MaChiTiet, TenChiTiet, SoLuongCT, ChieuDaySC, ChieuRongSC, ChieuDaiSC
                                            FROM          dbo.ChiTietSP) AS qry2 ON qry1.MaChiTiet = qry2.MaChiTiet";
                                        }
                                    }
                                    if (cb_MaCTReportTH.Text != "")
                                    {
                                        MaCTreport = prs.substring_right(cb_MaCTReportTH.Text, child);
                                        if (rd_XuongPhieuRpTH.Checked == true)
                                        {
                                            select = @" SELECT     qry1.MaKeHoach, qry1.MaChiTiet, qry2.TenSP, qry2.TenChiTiet, qry1.SLLP,qry1.NgayLP, qry1.SLXP,qry1.NgayXP, 
                                            qry1.SLLP * qry2.ChieuDaySC * qry2.ChieuRongSC * qry2.ChieuDaiSC * qry2.SoLuongCT / 1000000000 AS SoKhoi
                                             FROM         (SELECT     MaKeHoach, MaChiTiet, SUM(SLLP / SoLuongCT) AS SLLP,max(NgayLP) as NgayLP, SUM(ISNULL(SLXP, 0) / SoLuongCT) AS SLXP,max(NgayXP) as NgayXP
                                             FROM          dbo.KV2_NhapLPXP
                                             WHERE      (NgayXP >= '" + Ngay_Start + " 00:00:00') AND (NgayXP <= '" + Ngay_End + " 23:59:59') and MaChiTiet='" + MaCTreport + "'";
                                            select += @" GROUP BY MaKeHoach, MaChiTiet) AS qry1 inner JOIN
                                            (SELECT     MaSP, TenSP, MaChiTiet, TenChiTiet, SoLuongCT, ChieuDaySC, ChieuRongSC, ChieuDaiSC
                                            FROM          dbo.ChiTietSP) AS qry2 ON qry1.MaChiTiet = qry2.MaChiTiet";
                                        }
                                        if (rd_LenPhieuRpTH.Checked == true)
                                        {
                                            select = @" SELECT     qry1.MaKeHoach, qry1.MaChiTiet, qry2.TenSP, qry2.TenChiTiet, qry1.SLLP,qry1.NgayLP, qry1.SLXP,qry1.NgayXP, 
                                            qry1.SLLP * qry2.ChieuDaySC * qry2.ChieuRongSC * qry2.ChieuDaiSC * qry2.SoLuongCT / 1000000000 AS SoKhoi
                                             FROM         (SELECT     MaKeHoach, MaChiTiet, SUM(SLLP / SoLuongCT) AS SLLP,max(NgayLP) as NgayLP, SUM(ISNULL(SLXP, 0) / SoLuongCT) AS SLXP,max(NgayXP) as NgayXP
                                             FROM          dbo.KV2_NhapLPXP
                                             WHERE      (NgayLP >= '" + Ngay_Start + " 00:00:00') AND (NgayLP <= '" + Ngay_End + " 23:59:59') and MaChiTiet='" + MaCTreport + "'";
                                            select += @" GROUP BY MaKeHoach, MaChiTiet) AS qry1 inner JOIN
                                            (SELECT     MaSP, TenSP, MaChiTiet, TenChiTiet, SoLuongCT, ChieuDaySC, ChieuRongSC, ChieuDaiSC
                                            FROM          dbo.ChiTietSP) AS qry2 ON qry1.MaChiTiet = qry2.MaChiTiet";
                                        }
                                    }
                                }
                            }
                        }
                        else
                            MessageBox.Show("Bạn cần chọn thời gian tìm kiếm");

                    }
                    DataSet ds = prs.dts(select);
                    Fr_Report FrRp = new Fr_Report();
                    Ngay_Start = txt_datestartRpTH.Text;
                    Ngay_End = " - " + txt_dateendRpTH.Text;
                    //FrRp.report_KV2LXP(ds,Ngay_Start,Ngay_End);
                    FrRp.Show();
                    ds.Clear();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void bt_reportTH_Click(object sender, EventArgs e)
        {
            reportTH();
        }
        private void ExportExcel(DataTable dt, string sheetName)
        {

            Microsoft.Office.Interop.Excel.Application ExcelObj = new Microsoft.Office.Interop.Excel.Application();
            ExcelObj.Visible = true;
            Microsoft.Office.Interop.Excel.Workbook theWorkbook = ExcelObj.Workbooks.Open(@"C:\\Huynh\\NhapLXP.xlsx", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, false, Microsoft.Office.Interop.Excel.XlCorruptLoad.xlNormalLoad);
            Microsoft.Office.Interop.Excel.Sheets sheet = theWorkbook.Worksheets;
            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheet.get_Item(1);
            //worksheet.Activate();
            object[,] arr = new object[dt.Rows.Count, dt.Columns.Count];
            //Chuyển dữ liệu từ DataTable vào mảng đối tượng
            for (int r = 0; r < dt.Rows.Count; r++)
            {
                DataRow dr = dt.Rows[r];
                for (int c = 0; c < dt.Columns.Count; c++)
                {
                    //DataRow dr = dt.Rows[r];
                    arr[r, c] = dr[c];
                }
            }

            //Thiết lập vùng điền dữ liệu
            int rowStart = 6;
            int columnStart = 1;

            int rowEnd = rowStart + dt.Rows.Count - 1;
            //int rowEnd = rowStart + dt.Columns.Count - 1;

            int columnEnd = columnStart+dt.Columns.Count-1;
            //int columnEnd = dt.Rows.Count - 1 + columnStart;

            // Ô bắt đầu điền dữ liệu
            Microsoft.Office.Interop.Excel.Range c1 = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[rowStart, columnStart];
            // Ô kết thúc điền dữ liệu
            Microsoft.Office.Interop.Excel.Range c2 = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[rowEnd, columnEnd];
            // Lấy về vùng điền dữ liệu
            Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range(c1, c2);

            //Điền dữ liệu vào vùng đã thiết lập
            range.Value2 = arr;
            //range.Columns.AutoFit();
            range.Borders.LineStyle = Microsoft.Office.Interop.Excel.Constants.xlSolid;
            //theWorkbook=theWorkbook.Open("C:\\test.xlsx");
            //worksheet = ("sheet1");
            //range.Activate();
            object misValue = System.Reflection.Missing.Value;
            theWorkbook.Close(true, misValue, misValue);
            ExcelObj.Quit();

        }

        private void bt_BCCLCT_Click(object sender, EventArgs e)
        {
            DataTable dt = new DataTable();
            dt = report_CLCTNgay();
            dataGridView1.DataSource = dt;
        }

        private void bt_excel_Click(object sender, EventArgs e)
        {
            try
            {
                string Ngay_Start, Ngay_End;
                if (txt_datestartRp.Text != "" && txt_dateendRp.Text != "")
                {
                    string select_dt=null;
                    Ngay_Start = txt_datestartRp.Text.Trim();
                    Ngay_End = txt_dateendRp.Text.Trim();
                    string[] temp = Ngay_Start.Split('/');
                    Ngay_Start = temp[1] + "/" + temp[0] + "/" + temp[2];
                    string[] temp2 = Ngay_End.Split('/');
                    Ngay_End = temp2[1] + "/" + temp2[0] + "/" + temp2[2];
                    //MessageBox.Show(Ngay_Start + " " + Ngay_End);
                    Process prs = new Process();
                    if(rd_LenPhieu.Checked==true)
                        select_dt = @"select * from dbo.KV2_ExcelLXP where NgayLP>='" + Ngay_Start + " 00:00:00' and NgayLP<='" + Ngay_End + " 23:59:59'";
                    if(rd_XuongPhieu.Checked==true)
                        select_dt = @"select * from dbo.KV2_ExcelLXP where NgayXP>='" + Ngay_Start + " 00:00:00' and NgayXP<='" + Ngay_End + " 23:59:59'";
                    DataSet ds = prs.dts(select_dt);
                    ExportExcel(ds.Tables[0], "FileNhap");
                    //MessageBox.Show(select_count);
                }
                else
                    MessageBox.Show("Nhập Thời Gian Cần Xuất");
               
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            
        }
        public void Users(string Users)
        {
            lb_user.Text = Users;
        }
        private DataTable report_NSBao()
        {
            int j = 0, k = 0, i = 0;
            string select_count = @"select count(distinct(MaCT)) from dbo.NSSauBao", columnname, Ngay_Start, Ngay_End;
            Process prs = new Process();
            SqlConnection Conn = prs.Connect();
            DataTable dt = new DataTable();
            SqlCommand cmd = new SqlCommand();
            try
            {
                Conn.Open();
                cmd.Connection = Conn;
                cmd.CommandText = select_count;
                int row = int.Parse(cmd.ExecuteScalar().ToString());
                if (txt_datestart.Text != "" && txt_dateend.Text != "")
                {
                    Ngay_Start = txt_datestart.Text.Trim();
                    Ngay_End = txt_dateend.Text.Trim();
                    string[] temp = Ngay_Start.Split('/');
                    Ngay_Start = temp[1] + "/" + temp[0] + "/" + temp[2];
                    string[] temp2 = Ngay_End.Split('/');
                    Ngay_End = temp2[1] + "/" + temp2[0] + "/" + temp2[2];
                    select_count = @"select qry2.MaCT,qry1.TLDat,qry1.Ngay from
                                    (select NSSauBao.MaCT,avg(NSSauBao.TLDat) as TLDat,NSSauBao.Ngay from dbo.NSSauBao where Ngay>='" + Ngay_Start + " 00:00:00' and Ngay<='" + Ngay_End + " 23:59:59' ";
                    select_count+=@"group by NSSauBao.MaCT,NSSauBao.Ngay) as qry1
                                    right join (select MaCT from MaCTBao) as qry2 on qry2.MaCT=qry1.MaCT
                                    order by qry2.MaCT,qry1.Ngay";
                                   
                                    
                                    
                    //MessageBox.Show(select_count);
                }
                if (txt_datestart.Text == "" || txt_dateend.Text == "")
                {
                    MessageBox.Show("Vui Lòng Nhập Ngày Cần Tìm");
                }

                DataSet ds = prs.dts(select_count);
                int dsrows = ds.Tables[0].Rows.Count;
                //MessageBox.Show(ds.Tables[0].Rows[0][0].ToString());
                select_count = @"select max(qry1.dem) from (select count(MaCT) as dem from dbo.NSSauBao group by MaCT) as qry1";
                int rowdata = int.Parse(cmd.ExecuteScalar().ToString());
                int columndata = 3;
                dt = dta(columndata);
                //MessageBox.Show(dt.Columns.Count.ToString());
                int count = 0;
                while (i < row)
                {
                    DataRow dtrow = dt.NewRow();
                    j = 0;
                    columnname = j.ToString();
                    dtrow[columnname] = ds.Tables[0].Rows[count][0].ToString();
                    j++;
                    for (k = count; k < dsrows; k++)
                    {
                        if (k < dsrows - 1)
                        {
                            if (ds.Tables[0].Rows[k][0].ToString() == ds.Tables[0].Rows[k + 1][0].ToString())
                            {
                                for (int m = 1; m < ds.Tables[0].Columns.Count; m++)
                                {
                                    columnname = j.ToString();
                                    dtrow[columnname] = ds.Tables[0].Rows[k][m].ToString();
                                    //MessageBox.Show(dtrow["4"].ToString());
                                    j++;
                                }
                            }
                            if (ds.Tables[0].Rows[k][0].ToString() != ds.Tables[0].Rows[k + 1][0].ToString())
                            {
                                for (int m = 1; m < ds.Tables[0].Columns.Count; m++)
                                {
                                    columnname = j.ToString();
                                    dtrow[columnname] = ds.Tables[0].Rows[k][m].ToString();
                                    //MessageBox.Show(dtrow["4"].ToString());
                                    j++;
                                }

                                //MessageBox.Show("No Khac Rui Kia");
                                while (j < columndata)
                                {
                                    //
                                    columnname = j.ToString();
                                    dtrow[columnname] = "0";
                                    j++;
                                }
                                count = k + 1;
                                dt.Rows.Add(dtrow);
                                //MessageBox.Show(dt.Rows[0][18].ToString());
                                j = 0;
                                break;

                            }
                        }
                        if (k == dsrows - 1)
                        {
                            if (ds.Tables[0].Rows[k][0].ToString() == ds.Tables[0].Rows[k - 1][0].ToString())
                            {
                                for (int m = 1; m < ds.Tables[0].Columns.Count; m++)
                                {
                                    columnname = j.ToString();
                                    dtrow[columnname] = ds.Tables[0].Rows[k][m].ToString();
                                    //MessageBox.Show(dtrow["4"].ToString());
                                    j++;
                                }
                            }
                            if (ds.Tables[0].Rows[k][0].ToString() != ds.Tables[0].Rows[k - 1][0].ToString())
                            {
                                for (int m = 1; m < ds.Tables[0].Columns.Count; m++)
                                {
                                    columnname = j.ToString();
                                    dtrow[columnname] = ds.Tables[0].Rows[k][m].ToString();
                                    //MessageBox.Show(dtrow["4"].ToString());
                                    j++;
                                }

                                //MessageBox.Show("No Khac Rui Kia");
                                while (j < columndata)
                                {
                                    //
                                    columnname = j.ToString();
                                    dtrow[columnname] = "0";
                                    j++;
                                }
                                count = k + 1;
                                dt.Rows.Add(dtrow);
                                //MessageBox.Show(dt.Rows[0][18].ToString());
                                j = 0;
                                i = row;
                                break;

                            }
                        }
                        i++;
                    }
                }
                // MessageBox.Show(dt.Rows[20][4].ToString());
                //dataGridView1.DataSource = dt;



            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            return dt;
        }
        private void bt_NSBao_Click(object sender, EventArgs e)
        {
            DataTable dt = new DataTable();
            dt = report_NSBao();
            dataGridView1.DataSource = dt;
        }
        private void report_CTChuaXP()
        {
             try
             {
                 Process prs = new Process();
                 string Search = @"select * from dbo.KV2_CTChuaXP";
                 DataSet ds = prs.dts(Search);
                 Fr_Report Fr = new Fr_Report();
                 Fr.report_KV2TDTD(ds);
                 Fr.Show();
                 ds.Clear();
             }
             catch (Exception ex)
             {
                 MessageBox.Show(ex.Message);
             }
        }

        private void lb_user_TextChanged(object sender, EventArgs e)
        {
            Check_User();
        }

        private void dataGridView2_DoubleClick_1(object sender, EventArgs e)
        {
            if (dataGridView2.Rows.Count > 1)
            {
                try
                {
                    int r = dataGridView2.CurrentCell.RowIndex;
                    //MessageBox.Show(r.ToString());
                    Tong1 = double.Parse(lb_Tong.Text) - double.Parse(dataGridView2.Rows[r].Cells["SLLP"].Value.ToString());
                    lb_Tong.Text = string.Format("{0:###,###}", Tong1);
                    dataGridView2.Rows.RemoveAt(r);
                    //MessageBox.Show("aaaa");
                    int j = dataGridView2.Rows.Count - 1;
                    dataGridView2.CurrentCell = dataGridView2.Rows[j].Cells["TenCT"];
                }
                catch (Exception ex)
                { MessageBox.Show(ex.Message); }
            }
            if (dataGridView2.Rows.Count == 1)
                lb_Tong.Text = "0";
        }
        private DataTable report_KTam()
        {
            int j = 0, k = 0, i = 0;
            string select_count = @"select count(distinct(MaCT)) from dbo.KV2_THKH", columnname;
            Process prs = new Process();
            SqlConnection Conn = prs.Connect();
            DataTable dt = new DataTable();
            SqlCommand cmd = new SqlCommand();

            try
            {
                Conn.Open();
                cmd.Connection = Conn;
                cmd.CommandText = select_count;
                int row = 100;
                /*if (txt_datestart.Text != "" && txt_dateend.Text != "")
                {
                    Ngay_Start = txt_datestart.Text.Trim();
                    Ngay_End = txt_dateend.Text.Trim();
                    string[] temp = Ngay_Start.Split('/');
                    Ngay_Start = temp[1] + "/" + temp[0] + "/" + temp[2];
                    string[] temp2 = Ngay_End.Split('/');
                    Ngay_End = temp2[1] + "/" + temp2[0] + "/" + temp2[2];

                    //select_count = @"select MaCT,SoKH,min(NgayLP),datediff(HH,min(NgayLP),max(NgayLP)) as GioCLLP,sum(SLLP/SLChiTiet),sum(SLXP/SLChiTiet) from dbo.KV2_THKH where NgayLP>='"+Ngay_Start+" 00:00:00' and NgayLP<='"+Ngay_End+" 23:59:59' and SoKH>=83 group by MaCT,SoKH order by MaCT,SoKH";
                    if (txt_SoKH.Text != "")
                    {

                        select_count = @"select qry1.MaCT,qry1.SoKH,qry2.NgayKH_begin,qry2.GioCL,qry1.DBLP,qry1.DBXP 
                                    from (select MaCT,SoKH,sum(SLLP/SLChiTiet) as DBLP,sum(SLXP/SLChiTiet)as DBXP from dbo.KV2_THKH where SoKH>=" + txt_SoKH.Text + " and NgayLP>='" + Ngay_Start + " 00:00:00' and NgayLP<='" + Ngay_End + " 23:59:59' group by MaCT,SoKH)as qry1 ";
                        select_count += @" inner join (select SoKH,min(NgayLP)as NgayKH_begin,datediff(HH,min(NgayLP),max(NgayLP))as GioCL from dbo.KV2_THKH group by SoKH) as qry2
                                    on qry1.SoKH=qry2.SoKH
                                    order by MaCT,SoKH";
                    }
                    else
                    {
                        select_count = @"select qry1.MaCT,qry1.SoKH,qry2.NgayKH_begin,qry2.GioCL,qry1.DBLP,qry1.DBXP 
                                    from (select MaCT,SoKH,sum(SLLP/SLChiTiet) as DBLP,sum(SLXP/SLChiTiet)as DBXP from dbo.KV2_THKH where NgayLP>='" + Ngay_Start + " 00:00:00' and NgayLP<='" + Ngay_End + " 23:59:59' group by MaCT,SoKH)as qry1 ";
                        select_count += @" inner join (select SoKH,min(NgayLP)as NgayKH_begin,datediff(HH,min(NgayLP),max(NgayLP))as GioCL from dbo.KV2_THKH group by SoKH) as qry2
                                    on qry1.SoKH=qry2.SoKH
                                    order by MaCT,SoKH";
                    }
                }*/
                if (txt_datestart.Text == "" && txt_dateend.Text == "" && txt_SoKH.Text == "")
                {
                    select_count = @"select ChiTietSP.MaChiTiet,ChiTietSP.TenChiTiet,KV4_SLXuat.Ngay,'',left(KH_TheoDoi.ID_KeHoach,5),KH_TheoDoi.SLKH,isnull(KV4_SLXuat.SLXuat,0) as SLXuat,'' from (select distinct(ID_KeHoach) as SoKH from dbo.KH_TheoDoi where SLTheoDoi>0) as KH_Chon inner join dbo.KH_TheoDoi on KH_TheoDoi.ID_KeHoach=KH_Chon.SoKH
                                        left join (select KV4_KTam.SoKH,sum(SLXuat) as SLXuat,max(Ngay)as Ngay from dbo.KV4_KTam where SoKH is not null group by SoKH) as KV4_SLXuat on KV4_SLXuat.SoKH=KH_TheoDoi.ID inner join ChiTietSP on ChiTietSP.MaChiTiet=KH_TheoDoi.MaCT
                                            order by ChiTietSP.MaChiTiet,KH_TheoDoi.ID_KeHoach";
                  
                }

                DataSet ds = prs.dts(select_count);
                int dsrows = ds.Tables[0].Rows.Count;
                //MessageBox.Show(ds.Tables[0].Rows[0][0].ToString());
                //select_count = @"select max(qry1.dem) from (select count(MaCT) as dem from dbo.KV2_THKH group by MaCT) as qry1";
                //int rowdata = int.Parse(cmd.ExecuteScalar().ToString());
                int rowdata = 100;
                int columndata = rowdata * 2 + 1;
                dt = dta(columndata);
                //MessageBox.Show(dt.Columns.Count.ToString());
                int count = 0;
                while (i < row)
                {
                    DataRow dtrow = dt.NewRow();
                    j = 0;
                    columnname = j.ToString();
                    dtrow[columnname] = ds.Tables[0].Rows[count][0].ToString();

                    j++;
                    for (k = count; k < dsrows; k++)
                    {
                        if (k < dsrows - 1)
                        {

                            if (ds.Tables[0].Rows[k][0].ToString() == ds.Tables[0].Rows[k + 1][0].ToString())
                            {
                                for (int m = 1; m < ds.Tables[0].Columns.Count; m++)
                                {
                                    columnname = j.ToString();
                                    dtrow[columnname] = ds.Tables[0].Rows[k][m].ToString();
                                    //MessageBox.Show(dtrow["4"].ToString());
                                    j++;
                                }
                            }
                            if (ds.Tables[0].Rows[k][0].ToString() != ds.Tables[0].Rows[k + 1][0].ToString())
                            {
                                for (int m = 1; m < ds.Tables[0].Columns.Count; m++)
                                {
                                    columnname = j.ToString();
                                    dtrow[columnname] = ds.Tables[0].Rows[k][m].ToString();
                                    //MessageBox.Show(dtrow["4"].ToString());
                                    j++;
                                }

                                //MessageBox.Show("No Khac Rui Kia");
                                while (j < columndata)
                                {
                                    //
                                    columnname = j.ToString();
                                    dtrow[columnname] = "0";
                                    j++;
                                }
                                count = k + 1;
                                dt.Rows.Add(dtrow);
                                //MessageBox.Show(dt.Rows[0][18].ToString());
                                j = 0;
                                break;

                            }
                        }
                        if (k == dsrows - 1)
                        {
                            if (ds.Tables[0].Rows[k][0].ToString() == ds.Tables[0].Rows[k - 1][0].ToString())
                            {
                                for (int m = 1; m < ds.Tables[0].Columns.Count; m++)
                                {
                                    columnname = j.ToString();
                                    dtrow[columnname] = ds.Tables[0].Rows[k][m].ToString();
                                    //MessageBox.Show(dtrow["4"].ToString());
                                    j++;
                                }
                            }
                            if (ds.Tables[0].Rows[k][0].ToString() != ds.Tables[0].Rows[k - 1][0].ToString())
                            {
                                for (int m = 1; m < ds.Tables[0].Columns.Count; m++)
                                {
                                    columnname = j.ToString();
                                    dtrow[columnname] = ds.Tables[0].Rows[k][m].ToString();
                                    //MessageBox.Show(dtrow["4"].ToString());
                                    j++;
                                }

                                //MessageBox.Show("No Khac Rui Kia");
                                while (j < columndata)
                                {
                                    //
                                    columnname = j.ToString();
                                    dtrow[columnname] = "0";
                                    j++;
                                }
                                count = k + 1;
                                dt.Rows.Add(dtrow);
                                //MessageBox.Show(dt.Rows[0][18].ToString());
                                j = 0;
                                i = row;
                                break;
                            }
                        }
                    }

                    i++;
                }
                // MessageBox.Show(dt.Rows[20][4].ToString());
                //dataGridView1.DataSource = dt;



            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            return dt;
        }
        private void bt_KTam_Click(object sender, EventArgs e)
        {
            DataTable dt = new DataTable();
            dt = report_KTam();
            dataGridView1.DataSource = dt;
        }

        private void bt_LP_Click(object sender, EventArgs e)
        {
            string select_count = null,Ngay_Start,Ngay_End;
            Process prs = new Process();
            DataSet ds = new DataSet();
            //SqlConnection Conn = prs.Connect();
            try
            {
                if (txt_datestart.Text != "" && txt_dateend.Text != "")
                {
                    Ngay_Start = txt_datestart.Text.Trim();
                    Ngay_End = txt_dateend.Text.Trim();
                    string[] temp = Ngay_Start.Split('/');
                    Ngay_Start = temp[1] + "/" + temp[0] + "/" + temp[2];
                    string[] temp2 = Ngay_End.Split('/');
                    Ngay_End = temp2[1] + "/" + temp2[0] + "/" + temp2[2];
                    select_count = @"select dbo.MaCTTest.MaCT,SLLP,Ngay from
                    (select MaCT,sum(SLLP) as SLLP,Ngay from
                    (select MaCT,isnull(SLLP/SLChiTiet,0) as SLLP,convert(char(10),NgayLP,101) as Ngay
                    from dbo.KV2_THKH where NgayLP>='" + Ngay_Start + " 00:00:00' and NgayLP<='" + Ngay_End + " 23:59:59') as qry1 group by MaCT,Ngay) as qry2 right join dbo.MaCTTest on dbo.MaCTTest.MaCT=qry2.MaCT order by dbo.MaCTTest.MaCT";
                    
                    ds = prs.dts(select_count);
                    dataGridView1.DataSource = ds.Tables[0];
                }
                else
                    MessageBox.Show("Bạn cần nhập thời gian để in báo cáo");
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.Message);    
            }
        }

       

       

    }
}
